{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2022_09_29'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "day = datetime.datetime.strftime(datetime.datetime.now(), \"%Y_%m_%d\")\n",
    "day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 108632\r\n",
      "-rw-r--r--@ 1 ivan  staff   565464  7 30 18:56 eqList2022_07_30.xlsx\r\n",
      "-rw-r--r--@ 1 ivan  staff   566439  8  9 01:05 eqList2022_08_09.xlsx\r\n",
      "-rw-r--r--@ 1 ivan  staff  3567272  8 11 11:27 eqList2022_08_11.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff   566428  8 11 00:45 eqList2022_08_11.xlsx\r\n",
      "-rw-r--r--@ 1 ivan  staff  3568288  8 12 16:56 eqList2022_08_12.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3569648  8 13 15:09 eqList2022_08_13.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3573668  8 15 16:06 eqList2022_08_15.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3575011  8 16 18:58 eqList2022_08_16.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3576341  8 19 17:36 eqList2022_08_19.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3581086  8 26 10:12 eqList2022_08_26.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3585174  9  2 01:05 eqList2022_09_02.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3590551  9  5 16:37 eqList2022_09_05.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3603008  9 11 16:27 eqList2022_09_11.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3608412  9 17 20:52 eqList2022_09_17.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3615028  9 19 17:46 eqList2022_09_19.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3619386  9 22 18:31 eqList2022_09_22.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3624133  9 27 01:35 eqList2022_09_27.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3626487  9 29 16:55 eqList2022_09_29.xls\r\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'eqList/eqList2022_09_29.xls'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "! rm -rf \"eqList/eqList\"$day\".xls\"\n",
    "! cp \"/Users/ivan/Downloads/eqList\"$day\".xls\" \"eqList/eqList\"$day\".xls\"\n",
    "! ls -l \"eqList/\"\n",
    "\n",
    "aeta = f\"eqList/eqList{day}.xls\"\n",
    "aeta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 7\n",
    "T = 15\n",
    "# 经度\n",
    "lonL, lonH = 98, 107\n",
    "# 纬度\n",
    "latL, latH = 22, 34 \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "发震时刻       object\n",
      "震级(M)     float64\n",
      "纬度(°)     float64\n",
      "经度(°)     float64\n",
      "深度(千米)    float64\n",
      "参考位置       object\n",
      "dtype: object\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>发震时刻</th>\n",
       "      <th>震级(M)</th>\n",
       "      <th>纬度(°)</th>\n",
       "      <th>经度(°)</th>\n",
       "      <th>深度(千米)</th>\n",
       "      <th>参考位置</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-09-29 11:03:26</td>\n",
       "      <td>6.5</td>\n",
       "      <td>-60.50</td>\n",
       "      <td>-20.00</td>\n",
       "      <td>10.0</td>\n",
       "      <td>南桑威奇群岛</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-09-29 04:09:38</td>\n",
       "      <td>3.0</td>\n",
       "      <td>29.91</td>\n",
       "      <td>99.57</td>\n",
       "      <td>11.0</td>\n",
       "      <td>四川甘孜州巴塘县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-09-29 00:00:28</td>\n",
       "      <td>3.0</td>\n",
       "      <td>42.73</td>\n",
       "      <td>123.02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>辽宁沈阳市康平县（塌陷）</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-09-28 11:57:12</td>\n",
       "      <td>3.8</td>\n",
       "      <td>24.33</td>\n",
       "      <td>107.40</td>\n",
       "      <td>10.0</td>\n",
       "      <td>广西河池市东兰县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-09-27 22:08:35</td>\n",
       "      <td>5.0</td>\n",
       "      <td>40.80</td>\n",
       "      <td>42.85</td>\n",
       "      <td>10.0</td>\n",
       "      <td>土耳其</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-09-27 19:54:54</td>\n",
       "      <td>3.2</td>\n",
       "      <td>22.50</td>\n",
       "      <td>115.07</td>\n",
       "      <td>24.0</td>\n",
       "      <td>广东惠州市惠东县海域</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-09-27 11:16:10</td>\n",
       "      <td>3.2</td>\n",
       "      <td>31.90</td>\n",
       "      <td>104.19</td>\n",
       "      <td>12.0</td>\n",
       "      <td>四川绵阳市北川县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-09-26 20:57:37</td>\n",
       "      <td>3.4</td>\n",
       "      <td>41.18</td>\n",
       "      <td>83.26</td>\n",
       "      <td>14.0</td>\n",
       "      <td>新疆阿克苏地区库车市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2022-09-26 20:33:54</td>\n",
       "      <td>3.3</td>\n",
       "      <td>43.99</td>\n",
       "      <td>82.80</td>\n",
       "      <td>16.0</td>\n",
       "      <td>新疆伊犁州尼勒克县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022-09-26 17:59:58</td>\n",
       "      <td>5.5</td>\n",
       "      <td>54.15</td>\n",
       "      <td>-35.40</td>\n",
       "      <td>10.0</td>\n",
       "      <td>雷克雅内斯海岭</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2022-09-26 12:07:14</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.27</td>\n",
       "      <td>83.92</td>\n",
       "      <td>10.0</td>\n",
       "      <td>新疆阿克苏地区库车市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2022-09-26 08:50:32</td>\n",
       "      <td>3.2</td>\n",
       "      <td>42.21</td>\n",
       "      <td>80.54</td>\n",
       "      <td>8.0</td>\n",
       "      <td>新疆阿克苏地区温宿县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2022-09-25 19:16:31</td>\n",
       "      <td>5.4</td>\n",
       "      <td>4.30</td>\n",
       "      <td>126.25</td>\n",
       "      <td>130.0</td>\n",
       "      <td>印尼塔劳群岛</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022-09-25 08:35:52</td>\n",
       "      <td>3.2</td>\n",
       "      <td>40.16</td>\n",
       "      <td>83.13</td>\n",
       "      <td>10.0</td>\n",
       "      <td>新疆阿克苏地区沙雅县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2022-09-25 08:06:54</td>\n",
       "      <td>3.6</td>\n",
       "      <td>40.13</td>\n",
       "      <td>83.14</td>\n",
       "      <td>16.0</td>\n",
       "      <td>新疆阿克苏地区沙雅县</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   发震时刻  震级(M)  纬度(°)   经度(°)  深度(千米)          参考位置\n",
       "0   2022-09-29 11:03:26    6.5 -60.50  -20.00    10.0        南桑威奇群岛\n",
       "1   2022-09-29 04:09:38    3.0  29.91   99.57    11.0      四川甘孜州巴塘县\n",
       "2   2022-09-29 00:00:28    3.0  42.73  123.02     0.0  辽宁沈阳市康平县（塌陷）\n",
       "3   2022-09-28 11:57:12    3.8  24.33  107.40    10.0      广西河池市东兰县\n",
       "4   2022-09-27 22:08:35    5.0  40.80   42.85    10.0           土耳其\n",
       "5   2022-09-27 19:54:54    3.2  22.50  115.07    24.0    广东惠州市惠东县海域\n",
       "6   2022-09-27 11:16:10    3.2  31.90  104.19    12.0      四川绵阳市北川县\n",
       "7   2022-09-26 20:57:37    3.4  41.18   83.26    14.0    新疆阿克苏地区库车市\n",
       "8   2022-09-26 20:33:54    3.3  43.99   82.80    16.0     新疆伊犁州尼勒克县\n",
       "9   2022-09-26 17:59:58    5.5  54.15  -35.40    10.0       雷克雅内斯海岭\n",
       "10  2022-09-26 12:07:14    3.0  41.27   83.92    10.0    新疆阿克苏地区库车市\n",
       "11  2022-09-26 08:50:32    3.2  42.21   80.54     8.0    新疆阿克苏地区温宿县\n",
       "12  2022-09-25 19:16:31    5.4   4.30  126.25   130.0        印尼塔劳群岛\n",
       "13  2022-09-25 08:35:52    3.2  40.16   83.13    10.0    新疆阿克苏地区沙雅县\n",
       "14  2022-09-25 08:06:54    3.6  40.13   83.14    16.0    新疆阿克苏地区沙雅县"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "import xml\n",
    "DOMTree = xml.dom.minidom.parse(aeta).documentElement\n",
    "Row = DOMTree.getElementsByTagName(\"Row\")\n",
    "\n",
    "cols, data = [], []\n",
    "for nRow, iRow in enumerate(Row):\n",
    "    if nRow == 0:\n",
    "        for jRow in iRow.getElementsByTagName(\"Cell\"):\n",
    "            cols.append(jRow.childNodes[0].childNodes[0].data)\n",
    "    else:\n",
    "        idata = []\n",
    "        for jRow in iRow.getElementsByTagName(\"Cell\"):\n",
    "            idata.append(jRow.childNodes[0].childNodes[0].data)\n",
    "        data.append([float(dj) if nj in [1,2,3,4] else str(dj) for nj ,dj in enumerate(idata)])\n",
    "\n",
    "data = pd.DataFrame(data, columns=cols)\n",
    "print(data.dtypes)\n",
    "data.head(T)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def regeo(location):\n",
    "    try:\n",
    "        import json\n",
    "        import requests\n",
    "\n",
    "        host = 'https://regeo.market.alicloudapi.com'\n",
    "        path = '/v3/geocode/regeo'\n",
    "        appcode = '830fa522a512421eaa202bb80afe8921'\n",
    "        querys = f'location={location}' # 经度在前，纬度在后\n",
    "        with requests.get(\n",
    "            f\"{host}{path}?{querys}\", \n",
    "            headers={'Authorization': f'APPCODE {appcode}'}\n",
    "        ) as r:\n",
    "            j = json.loads(r.content.decode())[\"regeocode\"][\"formatted_address\"]\n",
    "            return j\n",
    "    except:\n",
    "        return \"NaN\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 15/15 [00:03<00:00,  4.08it/s]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "Kdata = []\n",
    "for STATION in tqdm(data.head(T)[\"参考位置\"]):\n",
    "    # idata\n",
    "    idata = data[data[\"参考位置\"] == STATION][[\"参考位置\", \"发震时刻\"]]\n",
    "    idata.rename({\n",
    "        \"参考位置\": \"初次参考位置\", \n",
    "        \"发震时刻\": \"初次发震时刻\",\n",
    "    }, axis=1, inplace=True)\n",
    "    idata[\"K\"] = 1\n",
    "    # print(idata.shape)\n",
    "\n",
    "    # jdata\n",
    "    jdata = data[\n",
    "        (data[\"参考位置\"] != STATION) & \n",
    "        (data[\"经度(°)\"] >= lonL) & (data[\"经度(°)\"] <= lonH) &\n",
    "        (data[\"纬度(°)\"] >= latL) & (data[\"纬度(°)\"] <= latH) &\n",
    "        # (data[\"震级(M)\"] >= 3.5) &\n",
    "        True\n",
    "    ][[\"参考位置\", \"震级(M)\", \"纬度(°)\", \"经度(°)\", \"发震时刻\"]]\n",
    "    jdata[\"K\"] = 1\n",
    "    # print(jdata.shape)\n",
    "\n",
    "    # k0data\n",
    "    k0data = pd.merge(\n",
    "        idata,\n",
    "        jdata,\n",
    "        on=\"K\"\n",
    "    )\n",
    "    # print(k0data.shape)\n",
    "\n",
    "    # k1data\n",
    "    # 筛选近N天内\n",
    "    k0data[\"gap-发震时刻\"] = (\n",
    "        pd.to_datetime(k0data[\"发震时刻\"]) - \n",
    "        pd.to_datetime(k0data[\"初次发震时刻\"])\n",
    "    ).dt.days\n",
    "\n",
    "    k1data = (k0data[\"gap-发震时刻\"] >= 1) & (k0data[\"gap-发震时刻\"] <= N)\n",
    "    # print(pd.value_counts(k1data))\n",
    "\n",
    "    k1data = k0data[k1data]\n",
    "    # print(k1data.shape)\n",
    "\n",
    "    # k2data\n",
    "    k2data = k1data.groupby(\n",
    "        [\"初次参考位置\", \"初次发震时刻\", \"K\"], \n",
    "        as_index=False\n",
    "    ).agg({\n",
    "        \"震级(M)\": \"mean\", # 特殊/余震\n",
    "        \"纬度(°)\": \"mean\",\n",
    "        \"经度(°)\": \"mean\",\n",
    "    }).groupby(\n",
    "        [\"K\"], \n",
    "        as_index=False\n",
    "    ).agg(\n",
    "        {\n",
    "        \"K\": \"sum\",\n",
    "        \"震级(M)\": \"mean\", \n",
    "        \"纬度(°)\": \"mean\",\n",
    "        \"经度(°)\": \"mean\",\n",
    "    })\n",
    "\n",
    "    k2data[\"参考位置\"] = STATION\n",
    "    k2data[\"K%\"] = k2data[\"K\"]/idata.shape[0]\n",
    "    k2data[\"震级C\"] = k2data[\"震级(M)\"].apply(lambda x: \"\" if x >= 3.5 else \"❌\")\n",
    "    k2data[\"经度C\"] = k2data[\"经度(°)\"].apply(lambda x: \"\" if x >= lonL and x <= lonH else \"❌\")\n",
    "    k2data[\"纬度C\"] = k2data[\"纬度(°)\"].apply(lambda x: \"\" if x >= latL and x <= latH else \"❌\")\n",
    "    k2data[\"经纬度解析\"] = [regeo(f\"{_1},{_2}\") for _1, _2 in zip(k2data[\"经度(°)\"], k2data[\"纬度(°)\"])]\n",
    "    k2data[\"经度(°)\"] = k2data[\"经度(°)\"].apply(lambda x: f\"{x:.6f}\")\n",
    "    k2data[\"纬度(°)\"] = k2data[\"纬度(°)\"].apply(lambda x: f\"{x:.6f}\")\n",
    "\n",
    "    k2data = k2data[\n",
    "        ['参考位置', '震级(M)', '震级C', '纬度(°)', '纬度C', '经度(°)', '经度C', 'K', 'K%','经纬度解析']\n",
    "    ]\n",
    "    # print(k2data)\n",
    "    Kdata.append(k2data)\n",
    "\n",
    "    # \n",
    "    del idata, jdata, k0data, k1data, k2data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>#发震时刻#</th>\n",
       "      <th>#震级(M)#</th>\n",
       "      <th>#纬度(°)#</th>\n",
       "      <th>#经度(°)#</th>\n",
       "      <th>#深度(千米)#</th>\n",
       "      <th>参考位置</th>\n",
       "      <th>震级(M)</th>\n",
       "      <th>震级C</th>\n",
       "      <th>纬度(°)</th>\n",
       "      <th>纬度C</th>\n",
       "      <th>经度(°)</th>\n",
       "      <th>经度C</th>\n",
       "      <th>K</th>\n",
       "      <th>K%</th>\n",
       "      <th>经纬度解析</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-09-29 11:03:26</td>\n",
       "      <td>6.5</td>\n",
       "      <td>-60.50</td>\n",
       "      <td>-20.00</td>\n",
       "      <td>10.0</td>\n",
       "      <td>南桑威奇群岛</td>\n",
       "      <td>3.545071</td>\n",
       "      <td></td>\n",
       "      <td>26.684699</td>\n",
       "      <td></td>\n",
       "      <td>103.138325</td>\n",
       "      <td></td>\n",
       "      <td>19</td>\n",
       "      <td>0.863636</td>\n",
       "      <td>云南省昭通市巧家县炉房乡腰岩</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-09-29 04:09:38</td>\n",
       "      <td>3.0</td>\n",
       "      <td>29.91</td>\n",
       "      <td>99.57</td>\n",
       "      <td>11.0</td>\n",
       "      <td>四川甘孜州巴塘县</td>\n",
       "      <td>3.991667</td>\n",
       "      <td></td>\n",
       "      <td>27.705417</td>\n",
       "      <td></td>\n",
       "      <td>100.733750</td>\n",
       "      <td></td>\n",
       "      <td>3</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>云南省丽江市宁蒗彝族自治县永宁镇</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-09-27 22:08:35</td>\n",
       "      <td>5.0</td>\n",
       "      <td>40.80</td>\n",
       "      <td>42.85</td>\n",
       "      <td>10.0</td>\n",
       "      <td>土耳其</td>\n",
       "      <td>3.515201</td>\n",
       "      <td></td>\n",
       "      <td>28.680898</td>\n",
       "      <td></td>\n",
       "      <td>102.598636</td>\n",
       "      <td></td>\n",
       "      <td>13</td>\n",
       "      <td>0.928571</td>\n",
       "      <td>四川省凉山彝族自治州越西县尔觉镇阿吉苦</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-09-27 19:54:54</td>\n",
       "      <td>3.2</td>\n",
       "      <td>22.50</td>\n",
       "      <td>115.07</td>\n",
       "      <td>24.0</td>\n",
       "      <td>广东惠州市惠东县海域</td>\n",
       "      <td>3.175000</td>\n",
       "      <td>❌</td>\n",
       "      <td>29.925000</td>\n",
       "      <td></td>\n",
       "      <td>100.622500</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>四川省甘孜藏族自治州雅江县柯拉乡</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-09-27 11:16:10</td>\n",
       "      <td>3.2</td>\n",
       "      <td>31.90</td>\n",
       "      <td>104.19</td>\n",
       "      <td>12.0</td>\n",
       "      <td>四川绵阳市北川县</td>\n",
       "      <td>3.167847</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.744167</td>\n",
       "      <td></td>\n",
       "      <td>102.581903</td>\n",
       "      <td></td>\n",
       "      <td>24</td>\n",
       "      <td>0.960000</td>\n",
       "      <td>四川省凉山彝族自治州普格县瓦洛乡004乡道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-09-26 20:57:37</td>\n",
       "      <td>3.4</td>\n",
       "      <td>41.18</td>\n",
       "      <td>83.26</td>\n",
       "      <td>14.0</td>\n",
       "      <td>新疆阿克苏地区库车市</td>\n",
       "      <td>3.447421</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.487692</td>\n",
       "      <td></td>\n",
       "      <td>102.973137</td>\n",
       "      <td></td>\n",
       "      <td>59</td>\n",
       "      <td>0.880597</td>\n",
       "      <td>四川省凉山彝族自治州美姑县候播乃拖镇</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-09-26 20:57:37</td>\n",
       "      <td>3.4</td>\n",
       "      <td>41.18</td>\n",
       "      <td>83.26</td>\n",
       "      <td>14.0</td>\n",
       "      <td>新疆阿克苏地区库车市</td>\n",
       "      <td>3.447421</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.487692</td>\n",
       "      <td></td>\n",
       "      <td>102.973137</td>\n",
       "      <td></td>\n",
       "      <td>59</td>\n",
       "      <td>0.880597</td>\n",
       "      <td>四川省凉山彝族自治州美姑县候播乃拖镇</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022-09-26 20:33:54</td>\n",
       "      <td>3.3</td>\n",
       "      <td>43.99</td>\n",
       "      <td>82.80</td>\n",
       "      <td>16.0</td>\n",
       "      <td>新疆伊犁州尼勒克县</td>\n",
       "      <td>3.265891</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.715990</td>\n",
       "      <td></td>\n",
       "      <td>102.596982</td>\n",
       "      <td></td>\n",
       "      <td>14</td>\n",
       "      <td>0.875000</td>\n",
       "      <td>四川省凉山彝族自治州普格县瓦洛乡004乡道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2022-09-26 17:59:58</td>\n",
       "      <td>5.5</td>\n",
       "      <td>54.15</td>\n",
       "      <td>-35.40</td>\n",
       "      <td>10.0</td>\n",
       "      <td>雷克雅内斯海岭</td>\n",
       "      <td>3.085714</td>\n",
       "      <td>❌</td>\n",
       "      <td>29.116190</td>\n",
       "      <td></td>\n",
       "      <td>102.079524</td>\n",
       "      <td></td>\n",
       "      <td>3</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>四川省甘孜藏族自治州九龙县湾坝镇549国道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2022-09-26 12:07:14</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.27</td>\n",
       "      <td>83.92</td>\n",
       "      <td>10.0</td>\n",
       "      <td>新疆阿克苏地区库车市</td>\n",
       "      <td>3.447421</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.487692</td>\n",
       "      <td></td>\n",
       "      <td>102.973137</td>\n",
       "      <td></td>\n",
       "      <td>59</td>\n",
       "      <td>0.880597</td>\n",
       "      <td>四川省凉山彝族自治州美姑县候播乃拖镇</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-09-26 12:07:14</td>\n",
       "      <td>3.0</td>\n",
       "      <td>41.27</td>\n",
       "      <td>83.92</td>\n",
       "      <td>10.0</td>\n",
       "      <td>新疆阿克苏地区库车市</td>\n",
       "      <td>3.447421</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.487692</td>\n",
       "      <td></td>\n",
       "      <td>102.973137</td>\n",
       "      <td></td>\n",
       "      <td>59</td>\n",
       "      <td>0.880597</td>\n",
       "      <td>四川省凉山彝族自治州美姑县候播乃拖镇</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2022-09-26 08:50:32</td>\n",
       "      <td>3.2</td>\n",
       "      <td>42.21</td>\n",
       "      <td>80.54</td>\n",
       "      <td>8.0</td>\n",
       "      <td>新疆阿克苏地区温宿县</td>\n",
       "      <td>3.279524</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.691786</td>\n",
       "      <td></td>\n",
       "      <td>102.957179</td>\n",
       "      <td></td>\n",
       "      <td>14</td>\n",
       "      <td>0.933333</td>\n",
       "      <td>四川省凉山彝族自治州甘洛县吉米镇曲米摇哄</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2022-09-25 19:16:31</td>\n",
       "      <td>5.4</td>\n",
       "      <td>4.30</td>\n",
       "      <td>126.25</td>\n",
       "      <td>130.0</td>\n",
       "      <td>印尼塔劳群岛</td>\n",
       "      <td>3.566852</td>\n",
       "      <td></td>\n",
       "      <td>28.427444</td>\n",
       "      <td></td>\n",
       "      <td>103.279815</td>\n",
       "      <td></td>\n",
       "      <td>9</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>四川省凉山彝族自治州雷波县谷堆乡163县道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022-09-25 08:35:52</td>\n",
       "      <td>3.2</td>\n",
       "      <td>40.16</td>\n",
       "      <td>83.13</td>\n",
       "      <td>10.0</td>\n",
       "      <td>新疆阿克苏地区沙雅县</td>\n",
       "      <td>3.382789</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.578308</td>\n",
       "      <td></td>\n",
       "      <td>102.190007</td>\n",
       "      <td></td>\n",
       "      <td>59</td>\n",
       "      <td>0.907692</td>\n",
       "      <td>四川省凉山彝族自治州冕宁县高阳街道菩萨渡</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2022-09-25 08:35:52</td>\n",
       "      <td>3.2</td>\n",
       "      <td>40.16</td>\n",
       "      <td>83.13</td>\n",
       "      <td>10.0</td>\n",
       "      <td>新疆阿克苏地区沙雅县</td>\n",
       "      <td>3.382789</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.578308</td>\n",
       "      <td></td>\n",
       "      <td>102.190007</td>\n",
       "      <td></td>\n",
       "      <td>59</td>\n",
       "      <td>0.907692</td>\n",
       "      <td>四川省凉山彝族自治州冕宁县高阳街道菩萨渡</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2022-09-25 08:06:54</td>\n",
       "      <td>3.6</td>\n",
       "      <td>40.13</td>\n",
       "      <td>83.14</td>\n",
       "      <td>16.0</td>\n",
       "      <td>新疆阿克苏地区沙雅县</td>\n",
       "      <td>3.382789</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.578308</td>\n",
       "      <td></td>\n",
       "      <td>102.190007</td>\n",
       "      <td></td>\n",
       "      <td>59</td>\n",
       "      <td>0.907692</td>\n",
       "      <td>四川省凉山彝族自治州冕宁县高阳街道菩萨渡</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2022-09-25 08:06:54</td>\n",
       "      <td>3.6</td>\n",
       "      <td>40.13</td>\n",
       "      <td>83.14</td>\n",
       "      <td>16.0</td>\n",
       "      <td>新疆阿克苏地区沙雅县</td>\n",
       "      <td>3.382789</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.578308</td>\n",
       "      <td></td>\n",
       "      <td>102.190007</td>\n",
       "      <td></td>\n",
       "      <td>59</td>\n",
       "      <td>0.907692</td>\n",
       "      <td>四川省凉山彝族自治州冕宁县高阳街道菩萨渡</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 #发震时刻#  #震级(M)#  #纬度(°)#  #经度(°)#  #深度(千米)#        参考位置  \\\n",
       "0   2022-09-29 11:03:26      6.5   -60.50   -20.00      10.0      南桑威奇群岛   \n",
       "1   2022-09-29 04:09:38      3.0    29.91    99.57      11.0    四川甘孜州巴塘县   \n",
       "2   2022-09-27 22:08:35      5.0    40.80    42.85      10.0         土耳其   \n",
       "3   2022-09-27 19:54:54      3.2    22.50   115.07      24.0  广东惠州市惠东县海域   \n",
       "4   2022-09-27 11:16:10      3.2    31.90   104.19      12.0    四川绵阳市北川县   \n",
       "5   2022-09-26 20:57:37      3.4    41.18    83.26      14.0  新疆阿克苏地区库车市   \n",
       "6   2022-09-26 20:57:37      3.4    41.18    83.26      14.0  新疆阿克苏地区库车市   \n",
       "9   2022-09-26 20:33:54      3.3    43.99    82.80      16.0   新疆伊犁州尼勒克县   \n",
       "10  2022-09-26 17:59:58      5.5    54.15   -35.40      10.0     雷克雅内斯海岭   \n",
       "8   2022-09-26 12:07:14      3.0    41.27    83.92      10.0  新疆阿克苏地区库车市   \n",
       "7   2022-09-26 12:07:14      3.0    41.27    83.92      10.0  新疆阿克苏地区库车市   \n",
       "11  2022-09-26 08:50:32      3.2    42.21    80.54       8.0  新疆阿克苏地区温宿县   \n",
       "12  2022-09-25 19:16:31      5.4     4.30   126.25     130.0      印尼塔劳群岛   \n",
       "13  2022-09-25 08:35:52      3.2    40.16    83.13      10.0  新疆阿克苏地区沙雅县   \n",
       "14  2022-09-25 08:35:52      3.2    40.16    83.13      10.0  新疆阿克苏地区沙雅县   \n",
       "15  2022-09-25 08:06:54      3.6    40.13    83.14      16.0  新疆阿克苏地区沙雅县   \n",
       "16  2022-09-25 08:06:54      3.6    40.13    83.14      16.0  新疆阿克苏地区沙雅县   \n",
       "\n",
       "       震级(M) 震级C      纬度(°) 纬度C       经度(°) 经度C   K        K%  \\\n",
       "0   3.545071      26.684699      103.138325      19  0.863636   \n",
       "1   3.991667      27.705417      100.733750       3  0.750000   \n",
       "2   3.515201      28.680898      102.598636      13  0.928571   \n",
       "3   3.175000   ❌  29.925000      100.622500       2  1.000000   \n",
       "4   3.167847   ❌  27.744167      102.581903      24  0.960000   \n",
       "5   3.447421   ❌  28.487692      102.973137      59  0.880597   \n",
       "6   3.447421   ❌  28.487692      102.973137      59  0.880597   \n",
       "9   3.265891   ❌  27.715990      102.596982      14  0.875000   \n",
       "10  3.085714   ❌  29.116190      102.079524       3  1.000000   \n",
       "8   3.447421   ❌  28.487692      102.973137      59  0.880597   \n",
       "7   3.447421   ❌  28.487692      102.973137      59  0.880597   \n",
       "11  3.279524   ❌  28.691786      102.957179      14  0.933333   \n",
       "12  3.566852      28.427444      103.279815       9  1.000000   \n",
       "13  3.382789   ❌  28.578308      102.190007      59  0.907692   \n",
       "14  3.382789   ❌  28.578308      102.190007      59  0.907692   \n",
       "15  3.382789   ❌  28.578308      102.190007      59  0.907692   \n",
       "16  3.382789   ❌  28.578308      102.190007      59  0.907692   \n",
       "\n",
       "                    经纬度解析  \n",
       "0          云南省昭通市巧家县炉房乡腰岩  \n",
       "1        云南省丽江市宁蒗彝族自治县永宁镇  \n",
       "2     四川省凉山彝族自治州越西县尔觉镇阿吉苦  \n",
       "3        四川省甘孜藏族自治州雅江县柯拉乡  \n",
       "4   四川省凉山彝族自治州普格县瓦洛乡004乡道  \n",
       "5      四川省凉山彝族自治州美姑县候播乃拖镇  \n",
       "6      四川省凉山彝族自治州美姑县候播乃拖镇  \n",
       "9   四川省凉山彝族自治州普格县瓦洛乡004乡道  \n",
       "10  四川省甘孜藏族自治州九龙县湾坝镇549国道  \n",
       "8      四川省凉山彝族自治州美姑县候播乃拖镇  \n",
       "7      四川省凉山彝族自治州美姑县候播乃拖镇  \n",
       "11   四川省凉山彝族自治州甘洛县吉米镇曲米摇哄  \n",
       "12  四川省凉山彝族自治州雷波县谷堆乡163县道  \n",
       "13   四川省凉山彝族自治州冕宁县高阳街道菩萨渡  \n",
       "14   四川省凉山彝族自治州冕宁县高阳街道菩萨渡  \n",
       "15   四川省凉山彝族自治州冕宁县高阳街道菩萨渡  \n",
       "16   四川省凉山彝族自治州冕宁县高阳街道菩萨渡  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "endata = pd.merge(\n",
    "    data.head(T).rename(\n",
    "        {icol: f\"#{icol}#\" for icol in data.columns if icol != \"参考位置\"}, \n",
    "        axis=1\n",
    "    ), \n",
    "    pd.concat(Kdata),\n",
    "    on=\"参考位置\"\n",
    ").sort_values(\"#发震时刻#\", ascending=False)\n",
    "\n",
    "endata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ">>> 2022-09-30 To 2022-10-06 / 2022-10-06\n",
      "\n",
      "# 有震预测 earthquake prediction\n",
      "check_my_prediction(myToken, '2022-09-30', '2022-10-06', 1, latitude=26.684699, longitude=103.138325, magnitude=3.5)\n",
      "\n",
      "\n",
      "# 有震预测 earthquake prediction\n",
      "check_my_prediction(myToken, '2022-09-30', '2022-10-06', 1, latitude=27.705417, longitude=100.733750, magnitude=4.0)\n",
      "\n",
      "\n",
      "# 有震预测 earthquake prediction\n",
      "check_my_prediction(myToken, '2022-09-30', '2022-10-06', 1, latitude=28.680898, longitude=102.598636, magnitude=3.5)\n",
      "\n",
      "\n",
      "# 有震预测 earthquake prediction\n",
      "check_my_prediction(myToken, '2022-09-30', '2022-10-06', 1, latitude=28.427444, longitude=103.279815, magnitude=3.6)\n",
      "\n",
      "\n",
      "# 无震预测 No earthquake prediction\n",
      "check_my_prediction(myToken, '2022-09-30', '2022-10-06', 0)\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# \n",
    "day1 = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(days=1), \"%Y-%m-%d\")\n",
    "dayn = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(days=N), \"%Y-%m-%d\")\n",
    "day7 = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(days=7), \"%Y-%m-%d\")\n",
    "print(f\">>> {day1} To {day7} / {dayn}\")\n",
    "\n",
    "message = f\"\"\"\n",
    "# 有震预测 earthquake prediction\n",
    "check_my_prediction(myToken, '{day1}', '{dayn}', 1, latitude=$_1, longitude=$_2, magnitude=$_3)\n",
    "\"\"\"\n",
    "endata = endata[endata[\"震级C\"] != \"❌\"]\n",
    "for _1, _2, _3 in zip(endata[\"纬度(°)\"], endata[\"经度(°)\"], endata[\"震级(M)\"]):\n",
    "    print(message\n",
    "          .replace(\"$_1\",f\"{float(_1):.6f}\")\n",
    "          .replace(\"$_2\",f\"{float(_2):.6f}\")\n",
    "          .replace(\"$_3\",f\"{float(_3):.1f}\")\n",
    "         )\n",
    "\n",
    "message = f\"\"\"\n",
    "# 无震预测 No earthquake prediction\n",
    "check_my_prediction(myToken, '{day1}', '{day7}', 0)\n",
    "\"\"\"\n",
    "print(message)\n",
    "print()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 744\r\n",
      "-rw-r--r--@ 1 ivan  staff  34490  8 16 18:58 Test002-2022_08_16.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  33275  8 19 17:36 Test002-2022_08_19.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  33096  8 26 10:12 Test002-2022_08_26.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  29782  9  2 01:05 Test002-2022_09_02.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff   8874  9  5 16:37 Test002-2022_09_05.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  43740  9 11 16:27 Test002-2022_09_11.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  32193  9 17 20:52 Test002-2022_09_17.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  27561  9 19 17:47 Test002-2022_09_19.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  40819  9 22 18:31 Test002-2022_09_22.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  30130  9 27 01:35 Test002-2022_09_27.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  41339  9 29 16:55 Test002-2022_09_29.ipynb\r\n"
     ]
    }
   ],
   "source": [
    "! cp Test002.ipynb \"back/Test002-\"$day\".ipynb\"\n",
    "! ls -l back/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
